import os.path as osp
from helper import *
import xlwings as xw
import pandas as pd


def write_to_excel(file_path, score_ach_dfs, obj_weight_df, report_df, summary_df, weights, statistic_df, figs):
    if not os.path.isdir(OUT_DIR):
        os.makedirs(OUT_DIR)
    out_file = osp.join(OUT_DIR, 'out_file.xls')
    app = xw.App(visible=False, add_book=False)
    app.display_alerts = False
    app.screen_updating = False
    wb = app.books.open(file_path)
    report_to_excel(wb, file_path, obj_weight_df, report_df)  # 写入课程目标达成情况报告
    achievement_to_excel(wb, file_path, score_ach_dfs)  # 写入达成度
    summary_df_to_excel(wb, summary_df, weights, statistic_df, figs)  # 写入达成度散点图页帧
    wb.save(out_file)
    wb.close()
    app.quit()
    logger.info('计算数据写入完成!')


def report_to_excel(wb, file_path, obj_weight_df, report_df, decimal=2):
    logger.info(f'{SECOND_SHEET_NAME}数据写入excel. . .')
    report_df = report_df.astype('float').round(decimal)
    sheet_df = pd.read_excel(io=file_path, sheet_name=SECOND_SHEET_NAME)
    (obj_ix, obj_iy), _ = loc_by_value(sheet_df, '考核方式')[-1]
    obj_iy = obj_iy + 2
    obj_ix = obj_ix + 2
    obj_num = obj_weight_df.shape[0]
    goal_num = obj_weight_df.shape[1]
    sub_num = report_df.shape[0] - obj_num
    (sub_ix, _), _ = loc_by_value(sheet_df, '课程目标达成度情况')[-1]
    sub_iy = obj_iy
    sub_ix = sub_ix + 2
    sht = wb.sheets[SECOND_SHEET_NAME]
    sht[obj_ix:obj_ix + obj_num, obj_iy:obj_iy + goal_num].value = report_df.iloc[0:obj_num, :].values
    sht[sub_ix:sub_ix + sub_num, sub_iy:sub_iy + goal_num].value = report_df.iloc[obj_num:, :].values
    sht[obj_ix:obj_ix + obj_num, obj_iy:obj_iy + goal_num].column_width = 11
    sht[obj_ix:obj_ix + obj_num, obj_iy:obj_iy + goal_num].row_height = 32
    sht[sub_ix:sub_ix + sub_num, sub_iy:sub_iy + goal_num].column_width = 11
    sht[sub_ix:sub_ix + sub_num, sub_iy:sub_iy + goal_num].row_height = 32
    sht[sub_ix:sub_ix + sub_num, sub_iy:sub_iy + goal_num].color = (255, 0, 255)
    sht[obj_ix:obj_ix + obj_num, obj_iy:obj_iy + goal_num].color = (255, 0, 255)


# 均值拼接
def _concat_mean_df(name, dfs, mean_dfs, class_ids):
    ach_df = dfs[name]
    ach_mean_df = mean_dfs[name]
    class_dfs = []
    for class_id, class_num in class_ids.items():
        class_ach_df = ach_df.loc[class_id, name]
        class_ach_mean_df = pd.DataFrame(ach_mean_df.loc[class_id, name]).T
        class_df = pd.concat([class_ach_df, class_ach_mean_df], axis=0, join='inner')
        lv1 = [class_id] * (class_num + 1)
        lv2 = list(class_ach_df.index)
        lv2.append('平均值')
        class_df.index = pd.MultiIndex.from_arrays([lv1, lv2], names=('班级', '学号'))
        class_dfs.append(class_df)
    ach_df = pd.concat(class_dfs, axis=0, join='inner')
    return ach_df


def merge_score_ach_df(name, score_df, ach_df):
    score_df['总分'] = score_df.sum(axis=1)
    score_df.columns = pd.MultiIndex.from_arrays([[f'{name}分数计算结果'] * len(score_df.columns), score_df.columns])
    ach_df.columns = pd.MultiIndex.from_arrays([[f'{name}达成度计算结果'] * len(ach_df.columns), ach_df.columns])
    return pd.merge(score_df, ach_df, left_index=True, right_index=True)


def achievement_to_excel(wb, file_path, score_ach_dfs, decimal=2):
    class_ids = score_ach_dfs[CLASS_IDS]
    ach_dfs, ach_mean_dfs = score_ach_dfs[ACH_INFO], score_ach_dfs[ACH_MEAN_INFO]
    score_dfs, score_mean_dfs = score_ach_dfs[SCORE_INFO], score_ach_dfs[SCORE_MEAN_INFO]
    sheet_names = ach_dfs[SECOND_SHEET_NAME].index
    for name in sheet_names:
        logger.info(f'{name}数据写入excel. . .')
        sht = wb.sheets[name]
        score_df = _concat_mean_df(name, score_dfs, score_mean_dfs, class_ids)
        ach_df = _concat_mean_df(name, ach_dfs, ach_mean_dfs, class_ids)
        result_df = merge_score_ach_df(name, score_df, ach_df).astype('float').round(decimal)
        start_col = pd.read_excel(io=file_path, sheet_name=name).shape[1] + 1
        end_col = start_col + ach_df.shape[1] + score_df.shape[1] + 2  # 二级索引宽度
        start_row = 2
        end_row = start_row + result_df.shape[0] + 2  # 二级索引宽度
        para1 = start_row + 2  # 二级索引宽度
        para2 = start_col + score_df.shape[1] + 2  # 二级索引宽度
        para3 = para2 + ach_df.shape[1]
        # index班级索引融合
        for class_id, class_num in class_ids.items():
            sht[para1:para1 + class_num + 1, start_col].api.Merge()
            sht[para1:para1 + class_num + 1, start_col].api.Font.Size = 12
            sht[para1:para1 + class_num + 1, start_col].api.Font.Bold = True
            para1 = para1 + class_num + 1
        # columns一级索引融合
        sht[start_row, start_col + 2:para2].api.Merge()
        sht[start_row, para2:para3].api.Merge()
        sht[start_row:start_row + 2, start_col:para3].api.Font.Size = 18
        sht[start_row:start_row + 2, start_col:para3].api.Font.Bold = True
        sht[start_row, start_col + 2:para2].row_height = 24
        sht[start_row:end_row, start_col:end_col].value = result_df
        sht[start_row:end_row, start_col:end_col].api.Borders.LineStyle = 1  # 框线
        sht[start_row:end_row, start_col:end_col].api.HorizontalAlignment = -4108  # 居中
        sht[start_row:end_row, start_col:end_col].api.VerticalAlignment = -4108
        sht[start_row:end_row, start_col:end_col].api.Font.Size = 10  # 字体大小
        sht[start_row:end_row, start_col:end_col].column_width = 11


def summary_df_to_excel(wb, summary_df, weights, statistic_df, figs, decimal=2):
    name = '新达成度散点图'
    logger.info(f'{name}数据写入excel. . .')
    summary_df = summary_df.astype('float').round(decimal)
    summary_df = insert_row(summary_df, 0, ('', '权重值'), weights)
    sht = wb.sheets.add(name)
    a, b, c = 2 + summary_df.shape[0], 2 + summary_df.shape[1], 2
    index = summary_df.index.get_level_values(0)
    column = summary_df.columns.get_level_values(0)
    counts = {}
    for i in index:
        counts[i] = counts.get(i, 0) + 1
    for key, value in counts.items():
        sht[c:c + value, 0].api.Merge()
        sht[c:c + value, 0].api.Font.Size = 12
        sht[c:c + value, 0].api.Font.Bold = True
        sht[c:c + value, 0].api.WrapText = True
        sht[c:c + value, 0].column_width = 9
        c = c + value
    counts.clear()
    c = 2
    for i in column:
        counts[i] = counts.get(i, 0) + 1
    for key, value in counts.items():
        sht[0, c:c + value].api.Merge()
        sht[0, c:c + value].api.Font.Size = 12
        sht[0, c:c + value].row_height = 24
        c = c + value

    sht[0:a, 0:b].api.Borders.LineStyle = 1  # 框线
    sht[0:3, 0:b].api.Font.Bold = True
    sht[0:a, 0:b].api.HorizontalAlignment = -4108  # 居中
    sht[0:a, 0:b].api.VerticalAlignment = -4108
    sht[2:a, 2:b].api.Font.Size = 10  # 字体大小
    sht[1:a, 1:b].column_width = 11
    e, f = a + 3 + statistic_df.shape[0], statistic_df.shape[1] + 1
    sht[a + 2:e, 0:f].api.WrapText = True
    sht[a + 2:e, 0:f].api.Borders.LineStyle = 1  # 框线
    sht[a + 2:e, 0:f].api.HorizontalAlignment = -4108  # 居中
    sht[a + 2:e, 0:f].api.VerticalAlignment = -4108
    sht[a + 2:e, 0:f].api.Font.Size = 10  # 字体大小
    sht[a + 2:e, 0:f].column_width = 11
    sht[a + 2:e, 0:f].row_height = 32
    sht[0:a, 0:b].value = summary_df  # 写入达成度
    sht[a + 2:a + statistic_df.shape[0], 0:f].value = statistic_df.astype('float').round(decimal)  # 写入统计

    g, h = b + 1, 2
    for i, figure in enumerate(figs):
        sht.pictures.add(figure, name='达成度散点图' + str(i), left=sht[h, g].left, top=sht[h, g].top)
        h = h + 25
